package com.yy.young.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcel {
	 /**!
     * // 分辨Excel后缀类型并调用相应方法读取Excel表的数据
     * @param file
     * @param fileName
     * @return List<List<List<String>>>
     * @throws IOException
     */
    public static List<List<List<String>>> readExcel(File file,String fileName) throws IOException{  
        //获取文件后缀名
    	String postfix = fileName.substring(fileName.lastIndexOf("."));
    	//result用来存放从Excel中读取到的数据
        List<List<List<String>>> result = new ArrayList<List<List<String>>>();
        //分辨文件后缀名类型
        if(".xls".equals(postfix)){
            result = readExcelXls(file);
        }else if(".xlsx".equals(postfix)){
            result = readExcelXlsx(file);
        }else{
            result = null;
        }
        return result;
    }
    /**!
     * !
     * //TODO 读取后缀为.xls的Excel文件
     * @param file
     * @return List<List<List<String>>>
     * @throws IOException
     */
    private static List<List<List<String>>> readExcelXls(File file) throws IOException{
        //result用来存放从Excel中读取到的数据
        List<List<List<String>>> result = new ArrayList<List<List<String>>>();
        //获得excel文件的IO流
        InputStream is = new FileInputStream(file);
        //InputStream is = new FileInputStream("c:\\user.xls");
        //得到后缀为.xls的excel对象
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        //循环每一页
        for(int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++){
            //HSSFSheet表示excel表格中的某一页
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            //resultSheet存放每一页的数据
            List<List<String>> resultSheet = new ArrayList<List<String>>();
            if(hssfSheet == null){//如果当前页为null,跳出当次循环;
                continue;
            }
            //处理当前页,循环读取每一行
            for(int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++){
                //HSSFRow表示该页中的某一行
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                //resultRow存放每一行的数据
                List<String> resultRow = new ArrayList<String>();
                if(hssfRow == null){
                    continue;
                }
                //最小列索引
                int minColIx = hssfRow.getFirstCellNum();
                //最大列索引
                int maxColIx = hssfRow.getLastCellNum();
                //遍历该行,获取每一格
                for(int colIx = minColIx; colIx <= maxColIx; colIx++){
                    //获得当前索引格
                    HSSFCell cell = hssfRow.getCell(colIx);
                    if(cell == null){
                        //保证结构固定
                        resultRow.add("");
                        continue;
                    }
                    //设置每个表格的类型为String                   
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    //将该格的值插入resultRow
                    resultRow.add(cell.getStringCellValue());
                }
                //将该行的值插入resultSheet
                resultSheet.add(resultRow);
            }
            //将该页的值插入result
            result.add(resultSheet);
        }
        return result;
    }
    
    /**!
     * !
     * //TODO 读取后缀为.xlsx的Excel文件
     * @param file
     * @return List<List<List<String>>>
     * @throws IOException
     */
    private static List<List<List<String>>> readExcelXlsx(File file) throws IOException{
        //result用来存放从Excel中读取到的数据
        List<List<List<String>>> result = new ArrayList<List<List<String>>>();
        //获得excel文件的IO流
        InputStream is = new FileInputStream(file);
        //得到后缀为.xlsx的excel对象
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        //读取每一页
        for(XSSFSheet xssfSheet : xssfWorkbook){
            //resultSheet存放每一页的数据
            List<List<String>> resultSheet = new ArrayList<List<String>>();
            if(xssfSheet == null){
                continue;
            }
            //读取每一行
            for(int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++){
                //得到行对象
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                //rowList存放每一行的数据
                List<String> resultRow = new ArrayList<String>();
                if(xssfRow == null){
                    continue;
                }
                //最小列索引
                int minColIx = xssfRow.getFirstCellNum();
                //最大列索引
                int maxColIx = xssfRow.getLastCellNum();
                //读取每一格
                for(int colIx = minColIx; colIx <= maxColIx; colIx++){
                    //得到格对象
                    XSSFCell cell = xssfRow.getCell(colIx);
                    if(cell == null){
                        //保证结构固定
                        resultRow.add("");
                        continue;
                    }
                    //设置每个表格的类型为String
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    //将得到的每一格的值插入到resultRow
                    resultRow.add(cell.toString());
                }
                //将读取到的每一行插入到resultSheet
                resultSheet.add(resultRow);
            }
            //将读取到的每一页插入到result
            result.add(resultSheet);
        }
        return result;
    }
}
